/**
 * 
 */
package ru.athena.runTool.Model;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Stack;

import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import javax.swing.tree.TreePath;

import ru.athena.runTool.blockTypes.CodeBlock;
import ru.athena.runTool.blockTypes.StoredObject;

/**
 * @author corc_usr
 *
 */
public class OperationStore implements Operation {
	private Connection		 		conn_;
	private String 					codeTreeTableName = "scriptsTreeTable";
	private CallableStatement		saveCodeTreeStmt = null;
	private Stack<BigDecimal> 		primKeys = new Stack<BigDecimal>();
	private String					treeID;
	
	public OperationStore( Connection conn, String treeID ) {
		this.conn_ = conn;
		this.treeID = treeID;
	}
	/** 
	 * implements saving of leaf node 
	 */
	public void visit(LeafNode leaf) throws Exception {
		saveLeaf((CodeBlock)leaf.getUserObject(),
						getCurrentParentPK() ) ;
	}

	/**
	 * restore prev parent primary key
	 */
	public void visitLeave(CompositeNode composite) throws Exception {
		primKeys.pop();		
	}	
	
	/** 
	 *  save composite as parent and store it primary key for saving 
	 *  it's children
	 */
	public void visitEnter(CompositeNode composite) throws Exception {
		primKeys.push(
					saveComposite( 
							(CodeBlock)composite.getUserObject(),
							getCurrentParentPK() ) );
	}

	/**
	 * 
	 */
	private BigDecimal getCurrentParentPK() {
		if ( primKeys.size() > 0  ) {
			return primKeys.peek();
		}
		return null;
	}

	private void saveLeaf( CodeBlock cb, BigDecimal parentPK  ) throws Exception {
		BigDecimal	primaryKey = null; // value ignored
		// primaryKey = save( treeID, parent, name, text, type );
		getCodeTreeSaveStmt().setBigDecimal( 1, primaryKey );
		getCodeTreeSaveStmt().setString( 2,	treeID );
		getCodeTreeSaveStmt().setBigDecimal( 3, parentPK );
		getCodeTreeSaveStmt().setString( 4, cb.getName() );		
		getCodeTreeSaveStmt().setString( 5, cb.getText() );
		getCodeTreeSaveStmt().setString( 6, cb.getClass().getName() );
		getCodeTreeSaveStmt().execute();
	}
	
	private BigDecimal saveComposite( CodeBlock cb, BigDecimal parentPK  ) throws Exception {
		// primaryKey = save( treeID, parent, name, text, type );
		getCodeTreeSaveStmt().registerOutParameter( 1, java.sql.Types.BIGINT );		
		getCodeTreeSaveStmt().setString( 2,	treeID );
		getCodeTreeSaveStmt().setBigDecimal( 3, parentPK );
		getCodeTreeSaveStmt().setString( 4, cb.getName() );		
		getCodeTreeSaveStmt().setString( 5, cb.getText() );
		getCodeTreeSaveStmt().setString( 6, cb.getClass().getName() );
		getCodeTreeSaveStmt().execute();
		return getCodeTreeSaveStmt().getBigDecimal(1);

	}
	
	private CallableStatement getCodeTreeSaveStmt() throws Exception {
		
		String seqenceName = "s_" + codeTreeTableName + "_PK";
		String triggerName = "t_" + codeTreeTableName + "_Ins";	
		String functionName = "save" + codeTreeTableName;		
		if ( saveCodeTreeStmt == null ) {
			try {
				StoredObject tab = new StoredObject( 
					"create table " + codeTreeTableName + "(\n" +
					"	ID			NUMBER PRIMARY KEY,\n" +
					"	TREE_ID		VARCHAR2(30) NOT NULL,\n" +
					"	PARENT		NUMBER REFERENCES " + codeTreeTableName + "(ID),\n" +
					"	NAME		VARCHAR2(254),\n" +
					"	TEXT		VARCHAR2(2000),\n" +
					"	TYPE		VARCHAR2(254)\n" +
					")" , codeTreeTableName );
				tab.execute(conn_);

				StoredObject seq = new StoredObject( 
						"create sequence " + seqenceName  +
						" minvalue 0 maxvalue 99999999999999999999 \n" +
						" increment by 1 start with 0 cache 20 noorder nocycle",
						seqenceName );
				seq.execute(conn_);
				
				/*
				StoredObject trig = new StoredObject( 
						"create trigger " + triggerName + "\n" + 
						" before insert or update on " + codeTreeTableName + "\n" +
						" for each row\n" +
						"begin\n" +
						"	if nvl(:new.ID,0) = 0 then\n" +
						"		select " + seqenceName + ".nextval into :new.ID from dual;\n" +
						"	end if;\n" +
						"end;",
						triggerName );
				System.out.println( trig.getText() );				
				trig.execute(conn_);
				*/								
			} catch ( SQLException e ) {
				System.out.println( e );
			}
			
			StoredObject proc = new StoredObject(
					"create or replace function " + functionName + "( " +
					"	treeID	varchar2,\n" +
					"	parent	number,\n" +
					"	name	varchar2,\n" +
					"	text	varchar2,\n" +
					"	type	varchar2)\n" +
					"return number as\n" +
					"	retVal		number;" +
					"begin\n" +
					"	insert into " + codeTreeTableName + "( id, tree_id, parent, name,text, type)\n" +
					" 		values( " + seqenceName + ".nextVal, treeID, parent,name,text,type)" +
					"		returning id into retVal;\n" +
					"	return retVal;" +
					"end " + functionName + ";",
					functionName );
			proc.execute(conn_);
			// primaryKey = save( treeID, parent, name, text, type ); 
			saveCodeTreeStmt = conn_.prepareCall(
					"begin " +
					"	? := " + functionName + "( ?, ?, ?, ?, ?);" +
					"end;");
		}
		return saveCodeTreeStmt;
	}
	
}
